Oracle sql*loader by Oracle sqlloader

Author:Oracle sqlloader [sqlloader, Oracle]
Language: fra
Format: epub
Tags: Informatique
Publisher: inconnu
Published: 2000-08-09T10:26:57+00:00

,ch05.25071 Page 116 Wednesday, April 11, 2001 1:44 PM

116

Chapter 5: Loading Delimited Data

FIELDS TERMINATED BY WHITESPACE OPTIONALLY ENCLOSED BY '"'

(

feature_name CHAR,

feature_type CHAR,

county CHAR,

latitude CHAR,

longitude CHAR,

elevation INTEGER EXTERNAL,

update_time DATE "YYYYMMDDHH24MI"

)

When WHITESPACE is used as the delimiter between fields, SQL*Loader will rec-

ognize any combination of space and tab characters as the delimiter. While often

helpful, there are two issues that arise out of this behavior:

•

Leading and trailing spaces are trimmed from any field that is terminated by

whitespace.

•

You can’t use two delimiters together to represent a null field.

The exception to these rules is that they really don’t apply when you use enclos-

ing characters such as quotation marks. Consider the following example:

"Big Powderhorn Mountain" " summit " "Gogebic"

"Lake Gogebic" "" "Ontonagon"

The data within the enclosing characters, quotation marks in this case, is pro-

tected. Thus, " summit " will retain its leading and trailing spaces. The feature type for Lake Gogebic will be interpreted as a null. The enclosing characters break up the string of whitespace, allowing SQL*Loader to recognize the field.

The keyword WHITESPACE cannot be used to specify an enclosing

character.

Example: Enclosing characters with no delimiters

It’s actually possible to specify enclosing characters for a field without specifying any termination characters, although it usually doesn’t make sense to do so. The

following data provides one possible case where it might make sense to use

ENCLOSED BY without first using TERMINATED BY:

"Big Powderhorn Mountain"(summit),Gogebic,

"Lake Gogebic"(reservoir),Ontonagon,

Each field in this record is enclosed, and in each case the enclosing characters are different. You can load this data using the following LOAD statement:

LOAD DATA

INFILE 'data07.dat'

This is the Title of the Book, eMatter Edition

Copyright © 2001 O’Reilly & Associates, Inc. All rights reserved.

,ch05.25071 Page 117 Wednesday, April 11, 2001 1:44 PM

Using Delimiters to Identify Fields

117

REPLACE INTO TABLE michigan_features

(

feature_name CHAR ENCLOSED BY '"',

feature_type CHAR ENCLOSED BY '(' AND ')',

county CHAR ENCLOSED BY ','

)

Now you might think that you could simplify things, and use the same enclosing

characters for each field. For example:

"Big Powderhorn Mountain""summit""Gogebic"

"Lake Gogebic""reservoir""Ontonagon"

Unfortunately, you can’t easily load this data because SQL*Loader interprets each back-to-back occurrence of the quotation mark character as a single quotation

mark to be included as part of the first field’s value. If you specified just

ENCLOSED BY '" ' for this data, then SQL*Loader would see each record as containing only one field. In order to load the data as three separate fields, you need to delimit it.

Example: Different beginning and ending enclosing characters

When you specify enclosing characters for a field, there’s no reason you have to

specify the same characters for the end of the field as for the beginning. Use the AND keyword, and you can specify both separately. Consider the following data:

(Big Powderhorn Mountain)(summit)(Gogebic)

(Lake Gogebic)(reservoir)(Ontonagon)

Each field is enclosed within parentheses, and of course the opening and closing

parentheses are not the same character. You can load this data using the follow-

ing LOAD statement:

LOAD DATA

INFILE 'data10.dat'

REPLACE INTO TABLE michigan_features

FIELDS ENCLOSED BY '(' AND ')'

(

feature_name CHAR,

feature_type CHAR,

county CHAR

)

The fact that there are no spaces or other delimiters between fields in this example doesn’t matter, because the beginning and ending enclosing characters are not the same.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.